package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashSet;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Set;

import modele.Formation;
import modele.Session;
import modele.Theme;

public class FormationDAO {

	private static final String	ID_FORMATION		= "id_formation";
	private static final String	CODE				= "code";
	private static final String	TITRE				= "titre";
	private static final String	OBJECTIF			= "objectif";
	private static final String	CONTENU				= "contenu";
	private static final String	CERTIFICATION		= "certification";
	private static final String	PREREQUIS			= "prerequis";

	private static final String	TABLE				= "`agriotes`.`formation`";

	static final String			SQL_INSERER			= "INSERT INTO " + TABLE + " (" + ID_FORMATION + "," + CODE + "," + TITRE + "," + OBJECTIF + ","
															+ CONTENU + "," + CERTIFICATION + "," + PREREQUIS + ") VALUES (NULL, ?, ?, ?, ?, ?, ?); ";

	static final String			SQL_MODIFIER		= "UPDATE  " + TABLE + " SET  " + CODE + " =  ?," + TITRE + " = ?," + OBJECTIF + " =  ?,"
															+ CONTENU + " =  ?," + CERTIFICATION + " = ?," + PREREQUIS + " =  ? WHERE  "
															+ ID_FORMATION + " = ? ;";

	static final String			SQL_SUPPRIMER		= "DELETE FROM " + TABLE + " WHERE ID_FORMATION = ? ;";
	static final String			SQL_CHERCHER		= "SELECT * FROM " + TABLE + " WHERE ID_FORMATION = ? ;";
	static final String			SQL_CHERCHER_CODE	= "SELECT * FROM " + TABLE + " WHERE " + CODE + " = ? ;";
	static final String			SQL_CHERCHER_MULTI	= "SELECT * FROM " + TABLE + " WHERE " + CODE + " LIKE ? OR  " + TITRE + " LIKE ? OR  "
															+ CERTIFICATION + " LIKE ? OR  " + PREREQUIS + " LIKE ? ; ";

	static final String			SQL_LISTE_TOUT		= "SELECT * FROM " + TABLE;
	private static Connection	connection;

	public static PreparedStatement getPreparedStatement(String sql) throws SQLException {

		return getPreparedStatement(sql, Statement.NO_GENERATED_KEYS);
	}

	public static PreparedStatement getPreparedStatement(String sql, int autoGeneratedKeys) throws SQLException {

		connection = FabriqueDAO.getConnection();
		return connection.prepareStatement(sql, autoGeneratedKeys);

	}

	/**
	 * méthode pour créer une nouvelle formation dans la base de données
	 * 
	 * @param f
	 */
	public static void inserer(Formation f) throws SQLException {

		// pour avoir autogenerated id
		// ajouter Statement.RETURN_GENERATED_KEYS
		PreparedStatement pt = getPreparedStatement(SQL_INSERER, Statement.RETURN_GENERATED_KEYS);

		pt.setString(1, f.getCode());
		pt.setString(2, f.getTitre());
		pt.setString(3, f.getObjectif());
		pt.setString(4, f.getContenu());
		pt.setString(5, f.getCertification());
		pt.setString(6, f.getPrerequis());

		pt.executeUpdate();

		// recuperer last inserted id by this connection

		ResultSet rs = pt.getGeneratedKeys();

		if (rs.next()) {
			// set id to this object
			f.setIdFormation(rs.getInt(1));

		}

		// close
		rs.close();
		pt.close();

		close();

	}

	/**
	 * méthode pour chercher les informations d'une formation selon son nom
	 * 
	 * @param id
	 * @return formation
	 */

	public static Formation lireParId(int id) throws SQLException {

		Formation f = null;

		PreparedStatement pt = getPreparedStatement(SQL_CHERCHER);
		pt.setInt(1, id);

		ResultSet rs = pt.executeQuery();
		if (rs.next()) {

			f = setFormationValues(rs);

		}

		// close
		rs.close();
		pt.close();

		close();

		return f;
	}

	/**
	 * méthode pour mettre à jour une formation de la base de données
	 * 
	 * @param formation
	 */
	public static void modifier(Formation p) throws SQLException {

		PreparedStatement pt = getPreparedStatement(SQL_MODIFIER);

		pt.setString(1, p.getCode());
		pt.setString(2, p.getTitre());
		pt.setString(3, p.getObjectif());
		pt.setString(4, p.getContenu());
		pt.setString(5, p.getCertification());
		pt.setString(6, p.getPrerequis());

		pt.setInt(7, p.getIdFormation());
		pt.executeUpdate();

		// close
		pt.close();

		close();

	}

	/**
	 * méthode pour effacer une formation de la base de données selon son nom
	 * 
	 * @param id
	 */
	public static void supprimer(int id) throws SQLException {

		SessionDAO sessionDao = new SessionDAO();

		for (Session session : sessionDao.listerParFormation(id)) {
			PreparedStatement pt = getPreparedStatement("DELETE FROM formateur_session WHERE id_session = ?");
			pt.setInt(1, session.getIdSession());
			pt.executeUpdate();
			pt.close();

			PreparedStatement pta = getPreparedStatement("DELETE FROM ressource WHERE id_session = ?");
			pta.setInt(1, session.getIdSession());
			pta.executeUpdate();
			pta.close();

			PreparedStatement ptb = getPreparedStatement("DELETE FROM preinscription WHERE id_session = ?;");
			ptb.setInt(1, session.getIdSession());
			ptb.executeUpdate();
			ptb.close();

			PreparedStatement ptc = getPreparedStatement("DELETE FROM session WHERE id_session = ?;");
			ptc.setInt(1, session.getIdSession());
			ptc.executeUpdate();
			ptc.close();
		}

		PreparedStatement ptd = getPreparedStatement("DELETE FROM formation_theme WHERE id_formation = ?");
		ptd.setInt(1, id);
		ptd.executeUpdate();
		ptd.close();

		PreparedStatement pte = getPreparedStatement(SQL_SUPPRIMER);
		pte.setInt(1, id);
		pte.executeUpdate();
		pte.close();

		close();

	}

	/**
	 * méthode pour lister toutes les formations de la table Formation
	 * 
	 * @return tableauFormation
	 */
	public static List<Formation> listerFormation() throws SQLException {

		ArrayList<Formation> tableauFormation = new ArrayList<>();

		PreparedStatement pt = getPreparedStatement(SQL_LISTE_TOUT);
		ResultSet rs = pt.executeQuery();

		while (rs.next()) {

			int id = rs.getInt(ID_FORMATION);
			String code = rs.getString(CODE);
			String titre = rs.getString(TITRE);
			String objectif = rs.getString(OBJECTIF);
			String contenu = rs.getString(CONTENU);
			String certification = rs.getString(CERTIFICATION);
			String prerequis = rs.getString(PREREQUIS);

			Formation f = new Formation(id, code, titre, objectif, contenu, certification, prerequis);

			tableauFormation.add(f);
		}

		// close
		rs.close();
		pt.close();

		close();

		return tableauFormation;
	}

	public static Formation lireParCode(String code) throws SQLException {
		
		Formation f = null;
		
		PreparedStatement pt = getPreparedStatement(SQL_CHERCHER_CODE);
		pt.setString(1, code);
		
		ResultSet rs = pt.executeQuery();
		if (rs.next()) {
			
			f = setFormationValues(rs);
			
		}
		
		// close
		rs.close();
		pt.close();
		
		close();
		return f;
	}
	
	public static Formation lireParTitre(String titre) throws SQLException {

		Formation f = null;

		PreparedStatement pt = getPreparedStatement("SELECT * FROM " + TABLE + " WHERE " + TITRE + " = ? ;");
		pt.setString(1, titre);

		ResultSet rs = pt.executeQuery();
		if (rs.next()) {

			f = setFormationValues(rs);

		}

		// close
		rs.close();
		pt.close();

		close();
		return f;
	}

	public static ArrayList<Formation> rechercheMultiWithTheme(String requete, boolean searchByTheme) throws SQLException {

		Set<Formation> formations = new HashSet<Formation>();
		formations.addAll(rechercheMulti(requete));

		if (searchByTheme) {
			formations.addAll(rechercheTheme(requete));
		}

		ArrayList<Formation> r = new ArrayList<Formation>();

		r.addAll(formations);

		Collections.sort(r, Formation.FormationNameComparator);

		return r;

		/*
		ArrayList<Formation> formations = new ArrayList<Formation>();
		formations.addAll(rechercheMulti(requete));

		if (searchByTheme) {
			formations.addAll(rechercheTheme(requete));
		}
		
		System.out.println("0000");
		for (Formation formation : formations) {
			System.out.println(formation.getIdFormation());
		}
		
		System.out.println("1111111111111");
		Set<Formation> s = new LinkedHashSet<Formation>(formations);

		ArrayList<Formation> r = new ArrayList<Formation>();
		for (Formation formation : s) {
			r.add(formation);
			System.out.println(formation.getIdFormation());
		}

		System.out.println("222222");

		return r;
		*/

	}

	public static ArrayList<Formation> rechercheTheme(String requete) throws SQLException {
		ThemeDAO themeDao = new ThemeDAO();
		List<Theme> themes = themeDao.recherche(requete);

		System.out.println(themes);
		String themesIds = "";
		for (Theme theme : themes) {
			themesIds += theme.getId_theme() + ", ";

		}

		return FormationThemeDAO.getFormationByThemes(themesIds);
	}

	public static ArrayList<Formation> rechercheMulti(String requete) throws SQLException {

		ArrayList<Formation> formations = new ArrayList<Formation>();

		// SQL_CHERCHER_MULTI pour tous les mots
		PreparedStatement pt = getPreparedStatement(SQL_CHERCHER_MULTI);
		pt.setString(1, "%" + requete + "%");
		pt.setString(2, "%" + requete + "%");
		pt.setString(3, "%" + requete + "%");
		pt.setString(4, "%" + requete + "%");

		ResultSet rs = pt.executeQuery();

		while (rs.next()) {
			formations.add(setFormationValues(rs));
		}

		// close
		rs.close();
		pt.close();

		close();

		return formations;
	}

	public static Formation setFormationValues(ResultSet rs) throws SQLException {
		Formation f = new Formation();
		f.setIdFormation(rs.getInt(ID_FORMATION));
		f.setCode(rs.getString(CODE));
		f.setTitre(rs.getString(TITRE));
		f.setObjectif(rs.getString(OBJECTIF));
		f.setContenu(rs.getString(CONTENU));
		f.setCertification(rs.getString(CERTIFICATION));
		f.setPrerequis(rs.getString(PREREQUIS));
		return f;
	}

	private static void close() throws SQLException {
		connection.close();
	}

	public static ArrayList<Formation> rechercheMultiMot(String requete) throws SQLException {

		String phrase = "test mest west";
		String[] mots = phrase.split("\\s");

		for (String mot : mots) {
			if (!mot.equals("")) {
				System.out.println(mot);
			}
		}

		return null;
	}
}
